package prosjekt;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

public class RestaurantDB {
	/**DBManager class for interaction with the database*/
	private static DBManager  dbm = DBManager.getInstance();
	/**The Connection for interaction with the database*/
	private static Connection con = dbm.getConnection();
	
	public RestaurantDB() {	}
	/**
	 * Adds a costumer in the database and updates it. 
	 * 
	 * @param fornavn - String, this costumers first name.
	 * @param etternavn - String, this costumers last name. 
	 * @param adresse - String, this costumers address.  
	 * @param husnr - int, this costumers address.
	 * @param postnr - int, this costumers postcode.
	 * @param poststed - String, this costumers city.
	 * @param tlf - int, this costumers phonenumber. 
	 * @return - addKunde returns an update of the database, 
	 * 			 whit all the information 
	 * about the costumer.
	 * @throws SQLException
	 */
	public static int setKunde (String fornavn,String etternavn,
			 String adresse, int husnr, int postnr,
			 String poststed, String tlf) throws SQLException {
		
		String query = "INSERT INTO kundeinfo (fornavn, etternavn, adresse, "+
			"husnr,postnr, poststed, tlfnr) VALUES (?,?,?,?,?,?,?);";
		PreparedStatement ps = con.prepareStatement(query);
		
		ps.setString(1, fornavn);
		ps.setString(2, etternavn);
		ps.setString(3, adresse);
		ps.setInt(4, husnr);
		ps.setInt(5, postnr);
		ps.setString(6, poststed);
		ps.setString(7, tlf);
		
		return ps.executeUpdate();
	}
	/**
	 * Adds a product in the database and updates it.
	 * 
	 * @param produktNavn - String, this products name.
	 * @param produktNummer - int, this products number.
	 * @param produktPris - String, this products price.
	 * @param kommentar  - String, this products comment.
	 * @return - returns the boolean true if there is added a new product. 
	 * @throws SQLException
	 */
 	public static boolean setProdukt (String produktNavn, int produktNummer, 
 			String produktPris,String kommentar) throws SQLException {
 		
		String query = "INSERT INTO menu(navn,nummer,pris,kommentar)"+
			" VALUES(?,?,?,?);";
		PreparedStatement ps = con.prepareStatement(query);
		
		ps.setString(1, produktNavn);
		ps.setInt(2, produktNummer);
		ps.setString(3, produktPris);
		ps.setString(4, kommentar);
		
		ps.executeUpdate();
		
		return true;
	}
 	
 	/**
	 * Set a given order as delivered in the DB
	 * @param ordreID
	 * @throws SQLException
	 */
	public static void setOrdreLevert(int ordreID) throws SQLException {
		String query = "UPDATE ordre "+
			"SET levert=1 WHERE ordre.ordreID=" + ordreID;
		PreparedStatement ps = con.prepareStatement(query);
		ps.executeUpdate();
	}
	/**
	 * Sets the state of the given products in the database to FINISHED.
	 * @param vareID -int[] the vareID number of the rows to change.
	 * @throws SQLException 
	 */
	public static void setVareFerdig(int vareID) throws SQLException{
		
		String query = 	"UPDATE vare SET ferdig = 1 WHERE vareID = ?;";
		PreparedStatement ps = con.prepareStatement(query);	
					
		ps.setInt(1, vareID);
		ps.executeUpdate();
	}
	/**
	 * List<KundeReg> getAlleKunder returns a list over all the costumers in 
	 * the database.
	 * @return - returns a list over all the costumers, whit kundeid, fornavn, 
	 * 			 etternavn, addresse,
	 * husnr, postnr, poststed and tlfnr.  
	 * @throws SQLException
	 */
	public static List<KundeReg> getAlleKunder() throws SQLException {
		List<KundeReg> liste = new ArrayList<KundeReg>();
		String query = "SELECT * FROM kundeinfo order by etternavn;";
		ResultSet rs = dbm.executeQuery(query);
		while (rs.next()){
			
			int kundeid		= rs.getInt(1);
			String fornavn 	= rs.getString(2);
			String etternavn  = rs.getString(3);
			String adresse  = rs.getString(4);
			int husnr = rs.getInt(5);
			int postnr = rs.getInt(6);
			String poststed = rs.getString(7);
			String tlf	= rs.getString(8);
			
			
			liste.add(new KundeReg(kundeid, fornavn, etternavn, 
					adresse, husnr,postnr, poststed,tlf));
		}
		
		return liste;
	}		
		/**
		 * List<ProduktReg> getAlleProdukter gets all the products in the 
		 * database.
		 * @return - returns a list over all the products, whit menuID, 
		 * 			 number, product name, price and 
		 * comment in the database.  
		 * @throws SQLException
		 */
		public static List<ProduktReg> getAlleProdukter() throws SQLException {
				List<ProduktReg> liste = new ArrayList<ProduktReg>();
				String query = "SELECT * FROM menu;";
				ResultSet rs = dbm.executeQuery(query);
				while (rs.next()){

					int menuID			= rs.getInt(1);
					int nummer 			= rs.getInt(2);
					String produktNavn  = rs.getString(3);
					String pris       	= rs.getString(4);
					String kommentar	= rs.getString(5);


					liste.add(new ProduktReg(menuID, nummer, produktNavn,
							pris, kommentar));
				}

				
				return liste;
		}
		
		/**
		 * List getProduktNr gets all the product numbers in the 
		 * mysql database.
		 * @return - returns a list over all the product numbers 
		 * @throws SQLException
		 */
		
		public static List<Integer> getAlleProduktNr() throws SQLException {
			List<Integer> liste = new ArrayList<Integer>();
			String query = "SELECT nummer FROM menu;";
			ResultSet rs = dbm.executeQuery(query);
			while (rs.next()){

				int nummer 			= rs.getInt(1);
				
				liste.add((nummer));
			}

			
			return liste;
	}
		
		/**
		 * static int method that gets the frakt value for settings table in mysql
		 * @throws SQLException
		 * @return frakt, int value
		 */
		public static int getFrakt() throws SQLException{
			
			String query = "SELECT verdi FROM settings WHERE "+
			"navn='frakt_kostnad'";
			PreparedStatement ps = con.prepareCall(query);
				
			ResultSet rs = ps.executeQuery();
			
			int frakt= 0;
			while (rs.next()){
				frakt = rs.getInt(1);
			}
			return frakt;
		}
		/**
		 * static int method that gets the grense value for settings table in mysql
		 * @throws SQLException
		 * @return grense, int value
		 */
		public static int getGrense() throws SQLException{
		
			String query = "SELECT verdi FROM settings WHERE "+
			"navn='frakt_grense'";
			
			PreparedStatement ps = con.prepareCall(query);
			ResultSet rs = ps.executeQuery();
			
			int grense= 0;
			while (rs.next()){
				grense = rs.getInt(1);
			}
			return grense;
		}
		/**
		 * getPris gets the price in the database for the selected produktNavn.
		 * @param produktNavn - String[] the productName number of the rows 
		 * 						to change. 
		 * @return - returns the selected products price.
		 * @throws SQLException
		 */
		public static String getPris (String produktNavn) throws SQLException{
	 		
			String query = "SELECT pris FROM menu WHERE navn=?";
			PreparedStatement ps = con.prepareCall(query);
			
			ps.setString(1, produktNavn);	
			ResultSet rs = ps.executeQuery();
			
			String pris="";
			while (rs.next()){
				pris = rs.getString(1);
			}
			return pris;
			
		}
		/**
		 * getKommentar gets comment in the database for the selected 
		 * productNavn.
		 * @param produktNavn - String[] the productName number of the rows 
		 * 						to change.
		 * @return - returns the selected products comment.
		 * @throws SQLException
		 */
		public static String getKommentar(String produktNavn) throws SQLException {
	 		
			String query = "SELECT kommentar FROM menu WHERE navn=?";
			PreparedStatement ps = con.prepareCall(query);
			
			ps.setString(1, produktNavn);	
			ResultSet rs = ps.executeQuery();
			
			String kommentar="";
			while (rs.next()){
				kommentar = rs.getString(1);
			}
			return kommentar;
		}
		/**
		 * Get info about the pizzas contained in the given order
		 * @param ordre OrdreID
		 * @return String vector containing pizzas with number and name from DB
		 * @throws SQLException
		 */
		public static Vector<String> getVareInfo(int ordre) throws SQLException {
			String query = "SELECT menu.nummer, menu.navn FROM `vare` " +
					"JOIN menu ON vare.menuID = menu.menuID " +
					"WHERE vare.ordreID = " +ordre+ ";";
			ResultSet rs = dbm.executeQuery(query);
			Vector<String> liste = new Vector<String>();
			
			while(rs.next()) {
				liste.add(rs.getString("menu.nummer")+": "+rs.getString("menu.navn"));
			}
			return liste;
		}
		/**
		 * Get info about customer from DB
		 * @param ordre ordreID
		 * @return ResultSet with customer information for the given order
		 * @throws SQLException
		 */
		public static ResultSet getKundeInfo(int ordre) throws SQLException {
			String query = "SELECT  * FROM  `ordre` " +
					"JOIN kundeinfo ON ordre.kundeID = kundeinfo.kundeID " +
					"WHERE ordre.ordreID = " +ordre+ ";";
			
			return dbm.executeQuery(query);
		}
		/**
		 * getProductNummer gets the number for the selected productNavn.
		 * @param produktNavn - String the productName number of the rows to 
		 * 						change.
		 * @return - returns the selected products number.
		 * @throws SQLException
		 */
		public static String getProduktNummer(String produktNavn) throws SQLException {
	 		
			String query = "SELECT nummer FROM menu WHERE navn=?";
			PreparedStatement ps = con.prepareCall(query);
			
			ps.setString(1, produktNavn);	
			ResultSet rs = ps.executeQuery();
			
			String nummer="";
			while (rs.next()){
				nummer = rs.getString(1);
			}
			return nummer;
		}
		/**
		 * Fetches all the ready orders that has not been delivered from the DB
		 * @return Integer vector containing all current orders ready for delivery
		 * @throws SQLException
		 */
		public static Vector<Integer> getLeveringsOrdre() throws SQLException {
			String query = "SELECT ordreID FROM ordre WHERE levering = 1 AND ordre.levert = 0 " +
					"AND ordre.ferdig > 0 ORDER BY ordreID;";
			
			ResultSet rs = dbm.executeQuery(query);
			
			Vector<Integer> liste = new Vector<Integer>();
			while(!rs.isClosed() && rs.next()) {
				liste.add(rs.getInt(1));
			}
			return liste;
		}
		/**
		 * fetches all the products from the database that not delivered
		 * @return ResultSet, the products 
		 * @throws SQLException
		 */
		public static ResultSet getOrders() 
			throws SQLException{
			
			String query = "SELECT menu.nummer, vare.vareID, menu.navn, "+
			"vare.komentar, ordre.ordreID, vare.ferdig FROM menu, vare, ordre "+
			"WHERE ordre.ordreID = vare.ordreID AND vare.menuID = menu.menuID "+
			"AND ordre.levert = 0 ORDER BY ordre.dato ASC;";
											
			return dbm.executeQuery(query);
		}
		/**
		 * fetches the product information for the given order from the 
		 * database. The {@link ResultSet} is poppulated as follows:
		 * <ol>
		 *  <li>menu navn &nbsp -String
		 *  <li>menu pris &nbsp&nbsp&nbsp -int
		 * </ol>
		 * @param ordreID -int The order to be fetched
		 * @return ResultSet -containing the product info for 
		 * the given order
		 * @throws SQLException
		 */
		public static ResultSet getUtleveringsOrdre(int ordreID) throws SQLException{
			String query = "SELECT menu.navn, menu.pris FROM menu, vare WHERE menu.menuID = vare.menuID AND vare.ordreID = ?;";
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, ordreID);

			return ps.executeQuery();
		}

		public static ResultSet getKunderUtenLevering() throws SQLException{
			String query = "SELECT ordre.ordreID, kundeinfo.fornavn, kundeinfo.etternavn FROM kundeinfo, ordre WHERE kundeinfo.kundeID = ordre.kundeID AND ordre.levering = 0 AND ordre.levert = 0 AND ordre.ferdig = 1 ORDER BY kundeinfo.etternavn ASC;";
		
			PreparedStatement ps = con.prepareStatement(query);
									
			return ps.executeQuery();
		}
		/**
		 * endreProduct changes this products name, price, number and comment.
		 * @param navn - String, this products name
		 * @param nyttNavn - String, this products new name
		 * @param pris - String, this products price
		 * @param nr - String, this products number.
		 * @param kommentar - String, this products comment 
		 * @return boolean - returns true if the information about the product 
		 * 					 is updated.
		 * @throws SQLException
		 */
		public static boolean endreProdukt(String navn,String nyttNavn, 
				String pris, String nr, String kommentar) throws SQLException {
			
			String query = "UPDATE menu "+
				"SET navn=?, pris=?, nummer=?, kommentar=? WHERE navn=?";
			PreparedStatement ps = con.prepareStatement(query);
			
			ps.setString(1, nyttNavn);
			ps.setString(2, pris);
			ps.setString(3, nr);
			ps.setString(4, kommentar);
			ps.setString(5, navn);
			
			ps.executeUpdate();
			return true;		
		}
		/**
		 * endreFrakt is used to change frakt and then updates the database. 
		 * @param frakt - String,  this delivery sum.
		 * @return - returns a boolean true if the frakt is changed and 
		 * 			 updated in the database.
		 * @throws SQLException
		 */
		public static boolean endreFrakt(String frakt) throws SQLException {
			
			String query = "UPDATE settings "+
				"SET verdi=? WHERE navn='frakt_kostnad'";
			PreparedStatement ps = con.prepareStatement(query);
			
				ps.setString(1, frakt);
			ps.executeUpdate();
			return true;		
		}
		/**
		 * endreGrense is used to change limits and then updates the database. 
		 * @param grense - String, this limits value.
		 * @return - returns a boolean true if the delivery is changed and 
		 * 			 updated in the database.
		 * @throws SQLException
		 */	
		public static boolean endreGrense(String grense) throws SQLException {
			
			String query = "UPDATE settings "+
				"SET verdi=? WHERE navn='frakt_grense'";
			PreparedStatement ps = con.prepareStatement(query);

				ps.setString(1, grense);
			
			ps.executeUpdate();
			return true;		
		}
		/**
		 * endreKunde is used to change information about a costumer.
		 * @param endreNavn 
		 * @param etternavn - String, the costumers last name
		 * @param fornavn - String, the costumers first name
		 * @param adresse - String, the costumers address
		 * @param husnr - String, the costumers house number
		 * @param postnr - String, the costumers city number
		 * @param poststed - String, the costumers city
		 * @param tlfnr - String, the costumers phone number. 
		 * @return - returns true if the costumer is changed
		 * @throws SQLException
		 */
		public static boolean endreKunde(String endreNavn, String etternavn, 
				String fornavn, String adresse, String husnr, String postnr, 
				String poststed, String tlfnr) throws SQLException {
			
			String query = "UPDATE kundeinfo "+
				"SET etternavn=?, fornavn=?, adresse=?, husnr=?, postnr=?, "+
				"poststed=?, tlfnr=? WHERE etternavn=?";
			PreparedStatement ps = con.prepareStatement(query);
			
			ps.setString(1, etternavn);
			ps.setString(2, fornavn);
			ps.setString(3, adresse);
			ps.setString(4, husnr);
			ps.setString(5, postnr);
			ps.setString(6, poststed);
			ps.setString(7, tlfnr);
			ps.setString(8, endreNavn);
			
			ps.executeUpdate();
			return true;		
		}
		/**
		 * removeProduct removes the given product from the table in the database.  
		 * @param produktNavn - String[] the productName number of the rows 
		 * 						to change.
		 * @return - returns an update of the database if ps = 1, and deletes 
		 * 			 the selected product.
		 * @throws SQLException
		 */
		public static int slettProdukt(String produktNavn) throws SQLException {
			
			String query = "DELETE FROM menu WHERE navn=?";
			PreparedStatement ps = con.prepareStatement(query);
			
			ps.setString(1, produktNavn);
			
			return ps.executeUpdate();
		}
		/**
		 * slettKunde removes the selected costumer. 
		 * @param etternavn- String, this costumers last name.
		 * @param fornavn -  String, this costumers first name.
		 * @return - slettKunde returns the new list after one costumer 
		 * 			 have been removed.
		 * @throws SQLException
		 */		
	 	public static int slettKunde(String etternavn, 
	 			String fornavn) throws SQLException {
	 		
	 		String query = "DELETE FROM kundeinfo WHERE etternavn=? "+
	 			"and fornavn=?";
	 		PreparedStatement ps = con.prepareStatement(query);
	 		
			ps.setString(1, etternavn);
			ps.setString(2, fornavn);
			
			return ps.executeUpdate();
		}
		/**
		 * Tests wether all the 'vare' with the given 'ordreID' has the state 
		 * 'ferdig'. If so, the 'ordre' itself wil be marked 'ferdig'
		 * @param ordreID -the 'ordre' to check and update.
		 * @throws SQLException
		 */
		public static void testAndSetOrdreFerdig(int ordreID) throws SQLException{
			boolean allDone = true;
			
			String query = 	"SELECT ferdig FROM vare WHERE ordreID = ?;";
			PreparedStatement ps = con.prepareStatement(query);	
						
			ps.setInt(1, ordreID);
			ResultSet rs = ps.executeQuery();
			
			//test if all the products in this order are done
			while(rs.next()){
				if(rs.getInt(1) == 0)
					allDone = false;
			}
			
			//if all products are done, set state of the order to done
			if(allDone){
				query = "UPDATE ordre SET ferdig = 1 WHERE ordreID = ?;";
				ps = con.prepareStatement(query);
				ps.setInt(1, ordreID);
				ps.executeUpdate();
			}
		}
	
}

